package com.uifp.gradebook;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.util.ArrayList;
import java.util.Iterator;

import com.uifp.gradebook.business_objects.Class;
import com.uifp.gradebook.business_objects.Grade;
import com.uifp.gradebook.business_objects.Heading;
import com.uifp.gradebook.business_objects.SemesterChild;
import com.uifp.gradebook.business_objects.SemesterParent;

import android.content.Context;
import android.database.Cursor;
import android.database.DatabaseUtils;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;



public class DataBaseHelper extends SQLiteOpenHelper{
	 
    //The Android's default system path of your application database.
    private static String DB_PATH = "/data/data/com.uifp.gradebook/databases/";
    private static String DB_NAME = "grades";
    private static SQLiteDatabase myDataBase = null; 
    private final Context myContext;

 
    /**
     * Constructor
     * Takes and keeps a reference of the passed context in order to access to the application assets and resources.
     * @param context
     */
    public DataBaseHelper(Context context) {
    	super(context, DB_NAME, null, 1);
        this.myContext = context;
    }	
 
  /**
     * Creates a empty database on the system and rewrites it with your own database.
     * */
    public void createDataBase() throws IOException{
 
    	boolean dbExist = checkDataBase();
 
	
    	if(dbExist){
    		//do nothing - database already exist
    		
    	}else{
 
    		//By calling this method and empty database will be created into the default system path
    		//of your application so we are gonna be able to overwrite that database with our database.
    		//this.myDataBase = getReadableDatabase();
    		
    		SQLiteDatabase tmpDB = this.getWritableDatabase();
    		tmpDB.close();
 
        	try {
 
    			//copyDataBase();
        		readSQLFile();
 
    		} catch (Exception e) {
 
        		throw new Error("Error copying database");
 
        	}

    	}
    }
 
    /**
     * Check if the database already exist to avoid re-copying the file each time you open the application.
     * @return true if it exists, false if it doesn't
     */
    private boolean checkDataBase(){
    	SQLiteDatabase checkDB = null;
 
    	try{
    		String myPath = DB_PATH + DB_NAME;
    		checkDB = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
 
    	}catch(SQLiteException e){
 
    		//database does't exist yet.
 
    	}
 
    	if(checkDB != null){
 
    		checkDB.close();
 
    	}
    	return checkDB != null ? true : false;
    }
 
    /**
     * Copies your database from your local assets-folder to the just created empty database in the
     * system folder, from where it can be accessed and handled.
     * This is done by transfering bytestream.
     * */
    @SuppressWarnings("unused")
	private void copyDataBase() throws IOException{
 
    	//Open your local db as the input stream
    	InputStream myInput = myContext.getAssets().open(DB_NAME);
 
    	// Path to the just created empty db
    	String outFileName = DB_PATH + DB_NAME;
 
    	//Open the empty db as the output stream
    	OutputStream myOutput = new FileOutputStream(outFileName);
 
    	//transfer bytes from the inputfile to the outputfile
    	byte[] buffer = new byte[1024];
    	int length;
    	while ((length = myInput.read(buffer))>0){
    		myOutput.write(buffer, 0, length);
    	}
 
    	//Close the streams
    	myOutput.flush();
    	myOutput.close();
    	myInput.close();
 
    }
    
    public void readSQLFile(){
    	openDataBaseRW();
    	try {
			SQLiteDBDeploy.deploy(myDataBase, myContext, "dbdump.zip");
    	} catch (IOException e) {
			throw new Error(e.getMessage());
		}
    	close();
    }
 
    public void openDataBase() throws SQLException{
    	//Open the database
        String myPath = DB_PATH + DB_NAME;
    	myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READONLY);
 
    }
 
    public void openDataBaseRW() throws SQLException{
    	//Open the database
        String myPath = DB_PATH + DB_NAME;
    	myDataBase = SQLiteDatabase.openDatabase(myPath, null, SQLiteDatabase.OPEN_READWRITE);
 
    }
 
    @Override
	public synchronized void close() {
 
    	    if(myDataBase != null)
    		    myDataBase.close();
 
    	    super.close();
 
	}
 
	@Override
	public void onCreate(SQLiteDatabase db) {
 
	}
 
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
 
	}

	public void removeClass(int classID){
		try {
			openDataBaseRW();
			String aSql = "UPDATE classes "
					+"SET enrolled = 0 "
					+"WHERE _id = " + classID;
			myDataBase.execSQL(aSql);
			close();
		}catch(SQLException sqlEx){
			Log.e("Database Helper Class", sqlEx.getMessage(), sqlEx);
		}
		finally{
			close();
		}
	}

	public void updateGrade(int gradeID, int value){
		try {
			openDataBaseRW();
			String aSql = "UPDATE grades "
					+"SET actual " + value
					+"WHERE _id = " + gradeID;
			myDataBase.execSQL(aSql);
			close();
		}catch(SQLException sqlEx){
			Log.e("Database Helper Class", sqlEx.getMessage(), sqlEx);
		}
		finally{
			close();
		}
	}	
	public void initGrades(int classID){
		try {
			openDataBaseRW();
			String aSql = "UPDATE classes "
					+"SET enrolled = 1 "
					+"WHERE _id = " + classID;
			myDataBase.rawQuery(aSql, null);
			close();
		}catch(SQLException sqlEx){
			Log.e("Database Helper Class", sqlEx.getMessage(), sqlEx);
		}
		finally{
			close();
		}
		
		ArrayList<Heading> list = new ArrayList<Heading>();
		Iterator<Heading> iter = list.iterator();
		
		while(iter.hasNext()){
			Heading heading = iter.next();
			try {
				openDataBase();
				String aSql = "INSERT INTO grades (actual, expected, target, weight, heading_id) " 
						+"values (0,100,0, "+ heading.getWeight()/heading.getCount() + ", " + heading.getHeading() 
						+ ")";
				myDataBase.execSQL(aSql);
				close();
			}catch(SQLException sqlEx){
				Log.e("Database Helper Class", sqlEx.getMessage(), sqlEx);
			}
			finally{
				close();
			}
		}
		
		
		
	}
	public void enroll(int classID){
		try {
			openDataBaseRW();
			String aSql = "UPDATE classes "
					+"SET enrolled = 1 "
					+"WHERE _id = " + classID;
			myDataBase.execSQL(aSql);
			close();
		}catch(SQLException sqlEx){
			Log.e("Database Helper Class", sqlEx.getMessage(), sqlEx);
		}
		finally{
			close();
		}
	}
	
	public Class getClassByID(int classID){
		Class aClass = new Class();
		Cursor aCursor = null;
		
		//get the info for the class fields.
		try {
			openDataBase();
			String aSql = " SELECT Classes._id, Semesters.name, Classes.year, "+
					" Courses.prefix, Courses.courseNum, Courses.name, "+
					" Courses.description, Professors.lname, Professors.fname, Classes.actual "+ 
					" FROM Classes "+
					" LEFT JOIN Semesters on Classes.semester_id = Semesters._id "+
					" LEFT JOIN Professors on Classes.professor_id = Professors._id "+
					" LEFT JOIN Courses on Classes.course_id = Courses._id " +
					" WHERE Classes._id = " + classID;
			aCursor = myDataBase.rawQuery(aSql, null);
//			DatabaseUtils.dumpCursor(aCursor);
	        if(aCursor.moveToFirst()){
				close();
	            do{ 
	            	String fname = aCursor.getString(7);
	            	String lname = aCursor.getString(8);
	            	aClass.setProf( fname + " " +lname);
	            	aClass.setName(aCursor.getString(5));
	            	aClass.setDesc(aCursor.getString(6));
	            }while(aCursor.moveToNext()); // moveToNext() moves the cursor to the next row
	        }
	        else{
	        	close();
	        }
	        aCursor.close();
		}catch(SQLException sqlEx){
			Log.e("Database Helper Class", sqlEx.getMessage(), sqlEx);
		}
		
		// create the grades list for the class
		try {
			ArrayList<Grade> list = new ArrayList<Grade>();
			openDataBase();
			String aSql = "SELECT headings.weight, grades._id, grades.actual, grades.expected, "
					+"categories.name, grades.heading_id, grades.target, grades._id "
					+"FROM Headings LEFT JOIN grades ON headings._id = grades.heading_id "
					+"LEFT JOIN categories on headings.category_id = categories._id "
					+"WHERE class_id = " + classID;
			aCursor = myDataBase.rawQuery(aSql, null);
//			DatabaseUtils.dumpCursor(aCursor);
	        if(aCursor.moveToFirst()){
				close();
	            do{
	            	Grade grade = new Grade();
	            	grade.setWeight(aCursor.getFloat(0));
	            	grade.setActualPts(aCursor.getInt(2));
	            	grade.setExpectedPts(aCursor.getInt(3));
	            	grade.setHeadingString(aCursor.getString(4));
	            	grade.setHeadingID(aCursor.getInt(5));
	            	grade.setTargetAvg(aCursor.getFloat(6));
	            	grade.setGradeID(aCursor.getInt(7));
	                list.add(grade);
	            }while(aCursor.moveToNext()); // moveToNext() moves the cursor to the next row
	            aClass.setGradeList(list);
	        }
	        else{
	        	close();
	        }
	        aCursor.close();
		}catch(SQLException sqlEx){
			Log.e("Database Helper Class", sqlEx.getMessage(), sqlEx);
		}	
		return aClass;	
	}
	
	//Create a list of SemesterParent items for classes that are enrolled
	/*
	 * SELECT Semesters.name, Classes.year FROM Classes
LEFT JOIN Semesters on Classes.semester_id = Semesters._id 
LEFT JOIN Courses on Classes.course_id = Courses._id 
WHERE classes.enrolled = 1
GROUP BY year, semesters.name
ORDER BY year DESC
	 */
	public ArrayList<SemesterParent> getEnrolledSemesters(Context tContext){
		ArrayList<SemesterParent> list = new ArrayList<SemesterParent>();
		Cursor aCursor = null;
		try {
			openDataBase();
			String aSql = "SELECT Semesters.name, Classes.year FROM Classes "
					+"LEFT JOIN Semesters on Classes.semester_id = Semesters._id "
					+"WHERE classes.enrolled = 1 "
					+"GROUP BY year, semesters.name "
					+"ORDER BY year DESC";
			aCursor = myDataBase.rawQuery(aSql, null);
//			DatabaseUtils.dumpCursor(aCursor);
	        if(aCursor.moveToFirst()){
				close();
	            do{
	                SemesterParent parent = new SemesterParent();
	                parent.setSemester(aCursor.getString(0));
	                parent.setYearString(aCursor.getString(1));
	                parent.setYear(aCursor.getInt(1));
	                
	                list.add(parent);
	            }while(aCursor.moveToNext()); // moveToNext() moves the cursor to the next row
	        }
	        else{
	        	close();
	        }
	        aCursor.close();
		}catch(SQLException sqlEx){
			Log.e("Database Helper Class", sqlEx.getMessage(), sqlEx);
		}
		
		return list;		
	}
	
	//create a list of strings that contains classes in a semester
	public ArrayList<SemesterChild> getSemesterClasses(Context tContext, String semester, String yearString){
		int year = Integer.parseInt(yearString);
		
		ArrayList<SemesterChild> courseList = new ArrayList<SemesterChild>();
		Cursor aCursor = null;
		try {
			openDataBase();
			String aSql = "SELECT Classes._id, Semesters.name, Classes.year, "
					+"Courses.prefix, Courses.courseNum, Courses.name, "
					+"Classes.actual FROM Classes "
					+"LEFT JOIN Semesters on Classes.semester_id = Semesters._id "
					+"LEFT JOIN Courses on Classes.course_id = Courses._id "

					+"WHERE semesters.name = '" + semester +"' "
					+"AND classes.year = " + year +" "
					+"AND classes.enrolled = 1";
			aCursor = myDataBase.rawQuery(aSql, null);
			DatabaseUtils.dumpCursor(aCursor);
	        if(aCursor.moveToFirst()){
				close();
	            do{ 
	            	SemesterChild child = new SemesterChild();
	                String label = aCursor.getString(3);
	                label += " " + aCursor.getString(4);
	                label += " " + aCursor.getString(5);
	                child.setClassId(aCursor.getInt(0));
	                child.setClassName(label);
	                courseList.add(child);
	            }while(aCursor.moveToNext()); // moveToNext() moves the cursor to the next row
	        }
	        else{
	        	close();
	        }
	        aCursor.close();
		}catch(SQLException sqlEx){
			Log.e("Database Helper Class", sqlEx.getMessage(), sqlEx);
		}
		
		return courseList;		
	}
	
	//return a cursor object with all of the classes in the application
	public Cursor getCourses(){
		
		Cursor aCursor = null;
		try {
			openDataBase();
			String aSql = " SELECT Classes._id, Semesters.name, Classes.year, "+
					" Courses.prefix, Courses.courseNum, Courses.name, "+
					" Courses.description, Professors.lname, Professors.fname, Classes.actual "+ 
					" FROM Classes "+
					" LEFT JOIN Semesters on Classes.semester_id = Semesters._id "+
					" LEFT JOIN Professors on Classes.professor_id = Professors._id "+
					" LEFT JOIN Courses on Classes.course_id = Courses._id "+
					" WHERE classes.enrolled = 0";
	/*
	 * 	SELECT Classes._id, Semesters.name, Classes.year, 
Courses.prefix, Courses.courseNum, Courses.name, 
Courses.description, Professors.lname, Professors.fname, Classes.actual FROM Classes
LEFT JOIN Semesters on Classes.semester_id = Semesters._id 
LEFT JOIN Professors on Classes.professor_id = Professors._id 
LEFT JOIN Courses on Classes.course_id = Courses._id
	 */
			aCursor = myDataBase.rawQuery(aSql, null);
			aCursor.moveToFirst();
			close();
		}catch(SQLException sqlEx){
			Log.e("Database Helper Class", sqlEx.getMessage(), sqlEx);
		}
		return aCursor;
	}

	public ArrayList<Heading> getHedings(int classID){
		ArrayList<Heading>  list = new ArrayList<Heading>();
		Cursor aCursor = null;
		try {
			openDataBase();
			String aSql = "SELECT  weight, expected_cnt, _id FROM headings WHERE class_id = " + classID;
			aCursor = myDataBase.rawQuery(aSql, null);
//			DatabaseUtils.dumpCursor(aCursor);
	        if(aCursor.moveToFirst()){
				close();
	            do{
	            	Heading heading = new Heading();
	            	heading.setWeight(aCursor.getFloat(0));
	            	heading.setCount(aCursor.getInt(1));
	            	heading.setHeading_id(aCursor.getInt(2));
	            	list.add(heading);
	            }while(aCursor.moveToNext()); // moveToNext() moves the cursor to the next row
	        }
	        else{
	        	close();
	        }
	        aCursor.close();
		}catch(SQLException sqlEx){
			Log.e("Database Helper Class", sqlEx.getMessage(), sqlEx);
		}
		return list;
	}
}